package oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestOracle {
	
	// 创建连接
	public Connection getConnection() {
		Connection conn = null;
		try {
			String url = "jdbc:oracle:thin:@localhost:1521:dbhouse";
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, "demo", "demo");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	// 执行SQL查询
	public void list() {
		try {
			Connection conn = getConnection();
			Statement stm = conn.createStatement();
			String sql = "select * from users";
			ResultSet rs = stm.executeQuery(sql);
			while (rs.next()) {
				String id = rs.getString("id");
				String username = rs.getString("username");
				String password = rs.getString("password");
				String email = rs.getString("email");
				System.out.println(id + "\t" + username + "\t" + password
						+ "\t" + email);
			}
			rs.close();
			stm.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// 执行单条查询
	public void select() {
		try {
			Connection conn = getConnection();
			Statement stm = conn.createStatement();
			String sql = "select * from users where id=1";
			ResultSet rs = stm.executeQuery(sql);
			if (rs.next()) {
				String id = rs.getString("id");
				String username = rs.getString("username");
				String password = rs.getString("password");
				String email = rs.getString("email");
				System.out.println(id + "\t" + username + "\t" + password
						+ "\t" + email);
			}
			rs.close();
			stm.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// 执行insert
	public void insert() {
		try {
			Connection conn = getConnection();
			Statement stm = conn.createStatement();
			String sql = "insert into users(id, username, password, email) values(user_sequence.nextval, 'test','123','test@163.com')";
			stm.executeUpdate(sql);
			stm.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// 执行update
	public void update() {
		try {
			Connection conn = getConnection();
			Statement stm = conn.createStatement();
			String sql = "update users set username='newtest', password='new123', email='newtest@163.com' where id=8";
			stm.executeUpdate(sql);
			stm.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// 执行delete
	public void delete() {
		try {
			Connection conn = getConnection();
			Statement stm = conn.createStatement();
			String sql = "delete from users where id=8";
			stm.executeUpdate(sql);
			stm.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		TestOracle test = new TestOracle();
		test.insert();
		test.update();
		test.list();
		test.select();
	}
}
